using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for UserAccDao
/// </summary>
public class UserAccDao
{
    public UserAccDao() { }

    public DataTable LoadAllUsers()
    {
        string proc = "sp_LoadAllUsers";
        DataSet ds = DbProvider.LoadData(proc);
        return ds.Tables[0];
    }

    public UserAcc GetDto(string userID)
    {
        string proc = "sp_LoadUser";
        SqlParameter p = new SqlParameter("@UserID", SqlDbType.VarChar);
        p.Value = userID;
        DataSet ds = DbProvider.LoadData(proc, p);
        if (ds.Tables[0].Rows.Count == 0) return null;

        DataRow row = ds.Tables[0].Rows[0];
        string pwd = row["PWD"].ToString();
        string fullName = row["FullName"].ToString();
        DateTime dateOfBirth = Convert.ToDateTime(row["DateOfBirth"]);
        string address = row["Address"].ToString();
        string idCard = row["IdCard"].ToString();
        string email = row["Email"].ToString();
        bool activated = Convert.ToBoolean(row["Activated"]);
        string cardType = row["CardType"].ToString();
        string cardOwner = row["CardOwner"].ToString();
        byte[] cardNo = (byte[])row["CardNo"];
        DateTime expiredDate = Convert.ToDateTime(row["ExpiredDate"]);
        bool disabled = Convert.ToBoolean(row["Disabled"]);
        UserAcc dto = new UserAcc(userID, pwd, fullName, dateOfBirth, address, idCard, email,
            "", activated, cardType, cardOwner, cardNo, expiredDate, disabled);
        return dto;
    }

    public void Delete(string userID)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_DelUser");
        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters["@UserID"].Value = userID;
        DbProvider.ExecCmd(cmd);
    }

    /// <summary>
    /// Update user's info (admin), no id updated
    /// </summary>
    public void Update(UserAcc dto)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_UpdateUser");
        
        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@FullName", SqlDbType.NVarChar);
        cmd.Parameters.Add("@DateOfBirth", SqlDbType.DateTime);
        cmd.Parameters.Add("@Address", SqlDbType.NVarChar);
        cmd.Parameters.Add("@Email", SqlDbType.VarChar);
        cmd.Parameters.Add("@Disabled", SqlDbType.Bit);

        cmd.Parameters["@UserID"].Value = dto.UserID;
        cmd.Parameters["@FullName"].Value = dto.FullName;
        cmd.Parameters["@DateOfBirth"].Value = dto.DateOfBirth;
        cmd.Parameters["@Address"].Value = dto.Address;
        cmd.Parameters["@Email"].Value = dto.Email;
        cmd.Parameters["@Disabled"].Value = dto.Disabled;

        DbProvider.ExecCmd(cmd);
    }

    public bool CheckUserID(string userID)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_CheckUserID");
        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@rs", SqlDbType.Bit).Direction = ParameterDirection.Output;
        cmd.Parameters["@UserID"].Value = userID;

        DbProvider.ExecCmd(cmd);
        bool rs = (bool)cmd.Parameters["@rs"].Value;
        return rs;
    }

    /// <summary>
    /// Validate dto, use in registration
    /// </summary>
    /// <param name="dto"></param>
    /// <returns></returns>
    public int CheckAccount(UserAcc dto)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_CheckAcc");
        cmd.Parameters.Add("@FullName", SqlDbType.NVarChar);
        cmd.Parameters.Add("@DateOfBirth", SqlDbType.DateTime);
        cmd.Parameters.Add("@IdCard", SqlDbType.VarChar);
        cmd.Parameters.Add("@Email", SqlDbType.VarChar);
        cmd.Parameters.Add("@rs", SqlDbType.Int).Direction = ParameterDirection.Output;

        cmd.Parameters["@FullName"].Value = dto.FullName;
        cmd.Parameters["@DateOfBirth"].Value = dto.DateOfBirth;
        cmd.Parameters["@IdCard"].Value = dto.IdCard;
        cmd.Parameters["@Email"].Value = dto.Email;

        DbProvider.ExecCmd(cmd);
        int rs = (int)cmd.Parameters["@rs"].Value;
        return rs;
    }

    public void Add(UserAcc acc)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_AddUserAcc");
        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@encryptedPWD", SqlDbType.VarChar);
        cmd.Parameters.Add("@FullName", SqlDbType.NVarChar);
        cmd.Parameters.Add("@DateOfBirth", SqlDbType.DateTime);
        cmd.Parameters.Add("@Address", SqlDbType.NVarChar);
        cmd.Parameters.Add("@IdCard", SqlDbType.VarChar);
        cmd.Parameters.Add("@Email", SqlDbType.VarChar);
        cmd.Parameters.Add("@ActivationCode", SqlDbType.VarChar);
        cmd.Parameters.Add("@Activated", SqlDbType.Bit);
        cmd.Parameters.Add("@CardType", SqlDbType.VarChar);
        cmd.Parameters.Add("@CardOwner", SqlDbType.NVarChar);
        cmd.Parameters.Add("@CardNo", SqlDbType.VarBinary);
        cmd.Parameters.Add("@ExpiredDate", SqlDbType.DateTime);
        cmd.Parameters.Add("@Disabled", SqlDbType.Bit);

        cmd.Parameters["@UserID"].Value = acc.UserID;
        cmd.Parameters["@encryptedPWD"].Value = acc.PWD;
        cmd.Parameters["@FullName"].Value = acc.FullName;
        cmd.Parameters["@DateOfBirth"].Value = acc.DateOfBirth;
        cmd.Parameters["@Address"].Value = acc.Address;
        cmd.Parameters["@IdCard"].Value = acc.IdCard;
        cmd.Parameters["@Email"].Value = acc.Email;
        cmd.Parameters["@ActivationCode"].Value = acc.ActivationCode;
        cmd.Parameters["@Activated"].Value = acc.Activated;
        cmd.Parameters["@CardType"].Value = acc.CardType;
        cmd.Parameters["@CardOwner"].Value = acc.CardOwner;
        cmd.Parameters["@CardNo"].Value = acc.CardNo;
        cmd.Parameters["@ExpiredDate"].Value = acc.ExpiredDate;
        cmd.Parameters["@Disabled"].Value = acc.Disabled;

        DbProvider.ExecCmd(cmd);
    }

    public bool ActivateAcc(string UserID, string Code)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_ActivateAcc");
        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@ActivationCode", SqlDbType.VarChar);
        cmd.Parameters.Add("@rs", SqlDbType.Bit).Direction = ParameterDirection.Output;

        cmd.Parameters["@UserID"].Value = UserID;
        cmd.Parameters["@ActivationCode"].Value = Code;

        DbProvider.ExecCmd(cmd);
        bool rs = (bool)cmd.Parameters["@rs"].Value;
        return rs;
    }

    public bool UserLogin(string userID, string encryptedPWD)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_UserLogin");

        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@encryptedPWD", SqlDbType.VarChar);
        cmd.Parameters.Add("@rs", SqlDbType.Bit).Direction = ParameterDirection.Output;

        cmd.Parameters["@UserID"].Value = userID;
        cmd.Parameters["@encryptedPWD"].Value = encryptedPWD;

        DbProvider.ExecCmd(cmd);
        return (bool)cmd.Parameters["@rs"].Value;
    }

    /// <summary>
    /// Update password for user (webparts)
    /// </summary>
    public void UpdatePass(string userID, string encryptedPWD)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_UpdateUserPass");
        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@PWD", SqlDbType.VarChar);

        cmd.Parameters["@UserID"].Value = userID;
        cmd.Parameters["@PWD"].Value = encryptedPWD;
        DbProvider.ExecCmd(cmd);
    }

    /// <summary>
    /// Validate new info of dto, use in editting info (webpart)
    /// </summary>
    public int CheckAccountWP(UserAcc dto)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_CheckAccWP");
        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@FullName", SqlDbType.NVarChar);
        cmd.Parameters.Add("@DateOfBirth", SqlDbType.DateTime);
        cmd.Parameters.Add("@IdCard", SqlDbType.VarChar);
        cmd.Parameters.Add("@Email", SqlDbType.VarChar);
        cmd.Parameters.Add("@rs", SqlDbType.Int).Direction = ParameterDirection.Output;

        cmd.Parameters["@UserID"].Value = dto.UserID;
        cmd.Parameters["@FullName"].Value = dto.FullName;
        cmd.Parameters["@DateOfBirth"].Value = dto.DateOfBirth;
        cmd.Parameters["@IdCard"].Value = dto.IdCard;
        cmd.Parameters["@Email"].Value = dto.Email;

        DbProvider.ExecCmd(cmd);
        int rs = (int)cmd.Parameters["@rs"].Value;
        return rs;
    }

    /// <summary>
    /// Update user's info (webpart)
    /// </summary>
    public void UpdateWP(UserAcc dto)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_UpdateUserWP");

        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@FullName", SqlDbType.NVarChar);
        cmd.Parameters.Add("@DateOfBirth", SqlDbType.DateTime);
        cmd.Parameters.Add("@Address", SqlDbType.NVarChar);
        cmd.Parameters.Add("@Email", SqlDbType.VarChar);
        cmd.Parameters.Add("@IdCard", SqlDbType.VarChar);
        cmd.Parameters.Add("@Disabled", SqlDbType.Bit);

        cmd.Parameters["@UserID"].Value = dto.UserID;
        cmd.Parameters["@FullName"].Value = dto.FullName;
        cmd.Parameters["@DateOfBirth"].Value = dto.DateOfBirth;
        cmd.Parameters["@Address"].Value = dto.Address;
        cmd.Parameters["@Email"].Value = dto.Email;
        cmd.Parameters["@IdCard"].Value = dto.IdCard;
        cmd.Parameters["@Disabled"].Value = dto.Disabled;

        DbProvider.ExecCmd(cmd);
    }

    public void UpdateCardWP(UserAcc dto)
    {
        SqlCommand cmd = DbProvider.CreateCommand("sp_UpdateCardWP");
        cmd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmd.Parameters.Add("@CardType", SqlDbType.VarChar);
        cmd.Parameters.Add("@CardOwner", SqlDbType.NVarChar);
        cmd.Parameters.Add("@CardNo", SqlDbType.VarBinary);
        cmd.Parameters.Add("@ExpiredDate", SqlDbType.DateTime);

        cmd.Parameters["@UserID"].Value = dto.UserID;
        cmd.Parameters["@CardType"].Value = dto.CardType;
        cmd.Parameters["@CardOwner"].Value = dto.CardOwner;
        cmd.Parameters["@CardNo"].Value = dto.CardNo;
        cmd.Parameters["@ExpiredDate"].Value = dto.ExpiredDate;
        DbProvider.ExecCmd(cmd);
    }
}